<?php
/**
 * Description of Usersclass
 *
 * @author Jeffy_shih
 * @copyright DoRISE team, Science Education center, NTNU.
 * @since 2010-02-24
 * @version 1.0
 */

class Topic {
    private $mysqli;

    function  __construct() {
        //引入資料庫連結參數設定
        include '../inc/oaConn.inc.php';
        $this->mysqli = @new mysqli($mysqlHost,$mysqlUser,$mysqlPassword,$mysqlDB);
        if(mysqli_connect_errno()) {
            throw new Exception('無法建立資料庫連線！'.mysqli_connect_error());
            $this->mysqli = false;
            exit();
        }

        //連線使用編碼
        $this->mysqli->query("SET NAMES 'utf8'");

    }

    public function getTopicBySubjectId($intSubjectId) {
        //檢查學科編號是否為空白
        if (empty ($intSubjectId)) {
            throw new Exception('缺少學科編號！');
        }

        $aryTopics = array();

        $sql_getTopicBySId = 'SELECT * FROM `topics` WHERE `subject_id`=' . $intSubjectId;
        $rs_getTopicBySId = $this->mysqli->query($sql_getTopicBySId);

        if(!$rs_getTopicBySId) {
            throw new Exception('無法查詢該學科的主題資料！');
        }else {
            while ($row_getTopicBySId = $rs_getTopicBySId->fetch_row()) {
                array_push($aryTopics, $row_getTopicBySId);
            }

            //釋放查詢結果
            $rs_getTopicBySId->free_result();
        }

        return $aryTopics;
    }

    public function getTopicList() {
        $aryTopic = array();

        $sql_getTopicList = 'SELECT `subjects`.`id`, `subjects`.`name_chinese`, `subjects`.`name`, `topics`.*
            FROM `topics`
            INNER JOIN `subjects` ON `topics`.`subject_id`=`subjects`.`id`
            ORDER BY `subjects`.`id`';
        $rs_getTopicList = $this->mysqli->query($sql_getTopicList);

        if(!$rs_getTopicList) {
            throw new Exception('無法查詢主題列表資料！');
        }else {
            while ($row_getTopicList = $rs_getTopicList->fetch_row()) {
                array_push($aryTopic, $row_getTopicList);
            }

            //釋放查詢結果
            $rs_getTopicList->free_result();
        }

        return $aryTopic;
    }

    public function getSingleTopicByTopicId($intTopicId) {
        //檢查主題編號是否為空白
        if (empty ($intTopicId)) {
            throw new Exception('缺少主題編號！');
        }

        $sql_getTopicById = 'SELECT * FROM `topics` WHERE `id`=' . $intTopicId;
        $rs_getTopicById = $this->mysqli->query($sql_getTopicById);

        if (!$rs_getTopicById) {
            throw new Exception('無法查詢該學科主題的資料！');
        }else {
            if ($rs_getTopicById->num_rows == 0) {
                throw new Exception('無此學科主題編號的資料！');
            }else {
                $row_getTopicById = $rs_getTopicById->fetch_row();
                return $row_getTopicById;
            }
        } //End If

    }

    public function addTopic($intSubjectId, $strEngName, $strChtName) {
        $bolRtn = false;

        //檢查傳入的主題名稱不可皆為空白
        if(empty ($strEngName) && empty ($strChtName)) {
            throw new Exception('主題名稱不可為空白！');
        }

        //查詢學科中是否有相同的主題
        $sql_chkDuplicateValue = 'SELECT * FROM `topics` WHERE `subject_id`=\''.$intSubjectId.'\' AND (`name`=\''.$strEngName.'\' OR `name_chinese`=\''.$strChtName.'\')';
        $rs_chkDuplicateValue = $this->mysqli->query($sql_chkDuplicateValue);

        if (!$rs_chkDuplicateValue) {
            throw new Exception('查詢是否有重複時發生錯誤！');
        }else {
            if ($rs_chkDuplicateValue->num_rows != 0) {
                throw new Exception('該學科已有相同的主題名稱存在！');
            }else {
                $strNow = $this->getNowTime();
                $sql_addTopic = 'INSERT INTO `topics` (`subject_id`, `name`, `name_chinese`, `created_date`)
                    VALUES('.$intSubjectId.',\''.$strEngName.'\',\''.$strChtName.'\',\''.$strNow.'\')';
                $rs_addTopic = $this->mysqli->query($sql_addTopic);
                if (!$rs_addTopic) {
                    throw new Exception('新增主題資料時發生錯誤！');
                }else {
                    $bolRtn = true;
                }
            }
        }

        return $bolRtn;
    }

    public function editTopic($intTopicId, $strEngName, $strChtName) {
        $bolRtn = false;
        //檢查主題編號
        if (empty ($intTopicId)) {
            throw new Exception('缺少學科編號！');
        }

        //檢查傳入的主題名稱是否為空白
        if(empty ($strEngName) || empty ($strChtName)) {
            throw new Exception('主題名稱不可為空白！');
        }

        $strNow = $this->getNowTime();
        $sql_uptTopic = 'UPDATE `topics` SET `name`=\''.$strEngName.'\',
            `name_chinese`=\'' . $strChtName . '\', 
            `modified_date`=\'' . $strNow . '\' 
            WHERE `id`=' . $intTopicId;
        if (!$this->mysqli->query($sql_uptTopic)) {
            throw new Exception('更新主題資料時發生錯誤！');
        }else {
            $bolRtn = true;
        }

        return $bolRtn;
    }

    public function delTopic($intTopicId) {
        $bolRtn = false;
        //檢查主題編號是否為空白
        if (empty ($intTopicId)) {
            throw new Exception('錯誤！缺少主題編號。');
        }

        //檢查主題是否在使用中
        $sql_chkUsed = 'SELECT * FROM `questionpool_question` WHERE `topic`=' . $intTopicId;
        $rs_chkUsed = $this->mysqli->query($sql_chkUsed);

        if (!$rs_chkUsed) {
            throw new Exception('異常！查詢主題是否為使用中時發生錯誤。');
        }else {
            if ($rs_chkUsed->num_rows > 0) {
                throw new Exception('主題項目正在使用中，不允許刪除。');
            }else {
                //非使用中的主題，予以刪除
                $sql_delTopic = 'DELETE FROM `topics` WHERE `id`=' . $intTopicId;
                $rs_delTopic = $this->mysqli->query($sql_delTopic);

                if (!$rs_delTopic) {
                    throw new Exception('錯誤！刪除主題時發生錯誤。');
                }else {
                    $bolRtn = true;
                }
            }
        }

        return $bolRtn;
    }

    public function getTopicNameByTId($intTopicId) {
        if(empty($intTopicId)) {
            throw new Exception('缺少查詢必要的主題編號！');
        }

        $sql_getTopicName = 'SELECT `name`, `name_chinese` FROM `topics` WHERE `id`=' . $intTopicId;
        $rs_getTopicName = $this->mysqli->query($sql_getTopicName);

        if(!$rs_getTopicName) {
            throw new Exception('查詢學科名稱時發生錯誤！');
        }else{
            $row_getTopicName = $rs_getTopicName->fetch_row();
            return $row_getTopicName;
        }
    }

    private function getNowTime() {
        return date('Y-m-d H:i:s');
    }

    function  __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->mysqli = FALSE;
        }
    }
}
?>
